using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using Microsoft.AceEngineering.SPM.SPMCustomDataEntity;
using Microsoft.ApplicationBlocks.Data;
using System.IO;
using System.Data.SqlTypes;
using System.Xml;

namespace Microsoft.AceEngineering.SPM.SPMDataLayer
{
    public class WTRIAssessmentDL
    {
        public static ApplicationCDE GetApplicationInfoByAppID(int appId)
        {
            SqlDataReader sqlReader = null;
            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@AppID", SqlDbType.Int);
                myParams[0].Value = appId;


                sqlReader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spgetAppInfoByID", myParams);
                ApplicationCDE appData = null;
                if (sqlReader.Read())
                {
                    appData = new ApplicationCDE();
                    appData.ApplicationID = Int32.Parse(sqlReader["ApplicationID"].ToString());
                    appData.ApplicationName = sqlReader["ApplicationName"].ToString();
                    appData.Version = sqlReader["Version"].ToString();
                    appData.Locked = sqlReader["Locked"].ToString();
                    appData.AssessmentData = sqlReader["AssesmentData"].ToString();
                }
                return appData;
            }
            finally
            {
                if (sqlReader != null)
                {
                    if (!sqlReader.IsClosed)
                        sqlReader.Close();
                }
            }
        }

        public static ThresholdCDE GetQuestionMaxAnswerWt()
        {
            SqlDataReader sqlReader = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                sqlReader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spGetQuestionMaxAnswerWt");

                ThresholdCDE thresholdData = null;
                if (sqlReader.Read())
                {
                    thresholdData = new ThresholdCDE();
                    thresholdData.MaxTotal = Int32.Parse(sqlReader["MaxTotal"].ToString());
                    thresholdData.MaxThreshold = Int32.Parse(sqlReader["MaxThreshold"].ToString());
                }
                return thresholdData;
            }
            finally
            {
                if (sqlReader != null)
                {
                    if (!sqlReader.IsClosed)
                        sqlReader.Close();
                }
            }
        }

        public static DataSet dsLoadDataSet(string strStoredProc)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            return SqlHelper.ExecuteDataset(conString, CommandType.StoredProcedure, strStoredProc);
        }

        public static DataSet dsLoadAssessmentDataSet(string strProc, int appID)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[1];
            myParams[0] = new SqlParameter("@AppID", SqlDbType.Int);
            myParams[0].Value = appID;

            return SqlHelper.ExecuteDataset(conString, CommandType.StoredProcedure, strProc, myParams);
        }


        public static int GetValidateScoreExist(int appID)
        {
            SqlDataReader sqlReader = null;
            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@AppID", SqlDbType.Int);
                myParams[0].Value = appID;

                sqlReader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spValidateScoreExists", myParams);
                if (sqlReader.Read())
                {
                    return Int32.Parse(sqlReader[0].ToString());
                }
                else
                {
                    return -1;
                }
            }
            finally
            {
                if (sqlReader != null)
                {
                    if (!sqlReader.IsClosed)
                        sqlReader.Close();
                }
            }
        }

        public static void AddResponses(Guid strID, MemoryStream xmlStream, int intAppID, bool blLocked, string loginName)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[6];
            myParams[0] = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
            myParams[0].Value = strID;

            myParams[1] = new SqlParameter("@AssessmentData", SqlDbType.Xml);
            myParams[1].Value = new SqlXml(new XmlTextReader(xmlStream));

            myParams[2] = new SqlParameter("@AppID", SqlDbType.Int);
            myParams[2].Value = intAppID;

            myParams[3] = new SqlParameter("@Locked", SqlDbType.Bit);
            myParams[3].Value = blLocked;

            myParams[4] = new SqlParameter("@CreatedBy", SqlDbType.VarChar, 50);
            myParams[4].Value = loginName;

            myParams[5] = new SqlParameter("@CreatedDate", SqlDbType.DateTime);
            myParams[5].Value = System.DateTime.Now.Date;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spaddResponses", myParams);
        }

        public static int RestictUpdatesToContacts(int appID, string alias)
        {
            SqlDataReader sqlReader = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[2];
                myParams[0] = new SqlParameter("@AppID", SqlDbType.Int);
                myParams[0].Value = appID;

                myParams[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
                myParams[1].Value = alias;

                sqlReader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spContactUser", myParams);

                sqlReader.Read();
                return Int32.Parse(sqlReader[0].ToString());
            }
            finally
            {
                if (sqlReader != null)
                {
                    if (!sqlReader.IsClosed)
                        sqlReader.Close();
                }
            }
        }

        public static void UpdateApplicationTaskDates(string xmlStream, int intAppID, string loginName)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[3];
            myParams[0] = new SqlParameter("@XmlDocument", SqlDbType.Xml);
            myParams[0].Value = xmlStream;

            myParams[1] = new SqlParameter("@AppID", SqlDbType.Int);
            myParams[1].Value = intAppID;

            myParams[2] = new SqlParameter("@alias", SqlDbType.VarChar, 50);
            myParams[2].Value = loginName;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spUpdateApplicationTaskDates", myParams);
        }
    }
}
